package cn.com.nes.common.util;

import org.springframework.jdbc.core.JdbcTemplate;

import java.sql.*;
import java.util.*;

public class XzqhUtil  {
	
	
	public static String getXzqhjb(Connection conn,String xzqydm) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		String res = "";
		try {
			sb.append("select XZQHJB from t_xzqh where XZQHDM ='")
					.append(xzqydm).append("' ");
			ps = conn
					.prepareStatement(sb.toString(),
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			if (rs.next()) {
				res = rs.getString("XZQHJB");
			}
		} finally {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
		}
		return res;
	}

	public static String getMinXzqhjb(Connection conn,String xzqydm) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		String res = "";
		try {
			String[] xzqhdmList = xzqydm.split(";",-1) ;
			if (xzqhdmList!=null & xzqhdmList.length > 0)
			{
				sb.append("select min(XZQHJB) XZQHJB from t_xzqh where XZQHDM in (");
				for (String xzqhdm : xzqhdmList) {
					sb.append("'").append(xzqhdm).append("',");
				}
				sb.deleteCharAt(sb.length() - 1);
				sb.append(") ");
				ps = conn
						.prepareStatement(sb.toString(),
								ResultSet.TYPE_SCROLL_SENSITIVE,
								ResultSet.CONCUR_READ_ONLY);
				rs = ps.executeQuery();
				if (rs.next()) {
					res = String.valueOf(Integer.parseInt(rs.getString("XZQHJB")) -1);
				}
			}

		} finally {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
		}
		return res;
	}

	
	public static String getQueryXzqh_mut(JdbcTemplate jdbcTemplate,String xzqhdm) throws SQLException
	{
		String result="NODATA";
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		String ret_xzqhdm="";
		try{
			if(xzqhdm==null || xzqhdm.equals("")){
				return  "" ;
			}
			String[] arr_xzqhdm=xzqhdm.split(",");
			for (int i=0;i<arr_xzqhdm.length;i++){
				    ret_xzqhdm="";
				    sb.delete(0, sb.length());
					sb.append("select XZQHJB from T_XZQH where XZQHDM= '").append(arr_xzqhdm[i]).append("'");
					
					List<Map<String, Object>> list = jdbcTemplate.queryForList(sb.toString(), new Object[]{});
					if(list.size()>0){
						int jb = Integer.parseInt(list.get(0).get("XZQHJB").toString()) ;
						if(jb==0)
							ret_xzqhdm= "" ;
						else if(jb==1)
							ret_xzqhdm= arr_xzqhdm[i].substring(0, 2) ;
						else if(jb==2)
							ret_xzqhdm= arr_xzqhdm[i].substring(0, 4);
						else if(jb==3)
							ret_xzqhdm = arr_xzqhdm[i].substring(0,6);
						else if(jb==4)
							ret_xzqhdm = arr_xzqhdm[i].substring(0,9);
						else if(jb==5)
							ret_xzqhdm = arr_xzqhdm[i];
						else if(jb==6)
							ret_xzqhdm = arr_xzqhdm[i];
						else
							ret_xzqhdm = arr_xzqhdm[i];
					}
					if (result!=null &&  "NODATA".equals(result)){
						result="^"+ret_xzqhdm;
					}else{
						result=result+'|'+"^"+ret_xzqhdm;
					}
			}
			
		}finally{
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
			}
		}
		return result;
	}
	public static String getQueryXzqh(JdbcTemplate jdbcTemplate,String xzqhdm) throws SQLException
	{
		String result="NODATA";
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		try{
			if(xzqhdm==null || xzqhdm.equals("")){
				return  "" ;
			}
			
			sb.append("select XZQHJB from T_XZQH where XZQHDM= '").append(xzqhdm).append("'");
			
			List<Map<String, Object>> list = jdbcTemplate.queryForList(sb.toString(), new Object[]{});
			if(list.size()>0){
				int jb = Integer.parseInt(list.get(0).get("XZQHJB").toString()) ;
				if(jb==0)
					result= "" ;
				else if(jb==1)
					result= xzqhdm.substring(0, 2) ;
				else if(jb==2)
					result= xzqhdm.substring(0, 4);
				else if(jb==3)
					result = xzqhdm.substring(0,6);
				else if(jb==4)
					result = xzqhdm.substring(0,9);
				else if(jb==5)
					result = xzqhdm;
				else if(jb==6)
					result = xzqhdm;
				else
					result = xzqhdm;
			}

			
		}finally{
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
			}
		}
		return result;
	}

	public static String getQueryXzqh(Connection conn, String xzqhdm) throws SQLException {
		String result = "NODATA";
		StringBuffer sb = new StringBuffer();
		try {
			if (xzqhdm == null || xzqhdm.equals("")) {
				return "";
			}

			sb.append("select XZQHJB from T_XZQH where XZQHDM= '")
					.append(xzqhdm).append("'");
			List<Map<String, Object>> res = queryForList(conn,
					sb.toString(), new Object[] {});

			if (res.size()==1) {
				String str_jb = (String) res.get(0).get("XZQHJB") ;
				int jb=Integer.parseInt(str_jb);
				if (jb == 0)
					result = "";
				else if (jb == 1)
					result = xzqhdm.substring(0, 2);
				else if (jb == 2)
					result = xzqhdm.substring(0, 4);
				else if (jb == 3)
					result = xzqhdm.substring(0, 6);
				else if (jb == 4)
					result = xzqhdm.substring(0, 9);
				else if (jb == 5)
					result = xzqhdm;
				else if (jb == 6)
					result = xzqhdm;
				else
					result = xzqhdm;
			}
		} finally {
		}
		return result;
	}

	public static String f_get_dwonQueryXzqh_report(String xzqhdm_fld,String xzqhjb) {
        int jb = Integer.parseInt(xzqhjb) ;
        StringBuffer sb = new StringBuffer() ;
        switch (jb) {
            case 0:
            	sb.append(" concat(substr(").append(xzqhdm_fld).append(",1,2),'') ") ;
                break;
            case 1:
            	sb.append(" concat(substr(").append(xzqhdm_fld).append(",1,4),'00000000') ") ;
                break;
            case 2:
                sb.append(" concat(substr(").append(xzqhdm_fld).append(",1,6),'000000') ") ;
                break;
            case 3:
                sb.append(" concat(substr(").append(xzqhdm_fld).append(",1,9),'000') ") ;
                break;
            case 4:
                sb.append(" substr(").append(xzqhdm_fld).append(",1,12) ") ;
                break;
            default:
                sb.append(" ").append(xzqhdm_fld).append(" ") ;
                break;
        }
        return sb.toString() ;
    }
	
	public static String getMultipleQueryXzqh(JdbcTemplate jdbcTemplate,String xzqhdm,String fieldName,String ... uid) throws SQLException
	{
		String result="NODATA";
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		StringBuffer con = new StringBuffer();
		try{
			if(xzqhdm==null || xzqhdm.equals("")){
				return  "" ;
			}
			String[] xzqhdmList = xzqhdm.split(";",-1) ;
			for(int i=0;i<xzqhdmList.length;i++) {
				xzqhdm = xzqhdmList[i] ;
				sb.delete(0, sb.length()) ;
				sb.append("select XZQHJB from T_XZQH where XZQHDM= '").append(xzqhdmList[i]).append("'");
				
				List<Map<String, Object>> list = jdbcTemplate.queryForList(sb.toString(), new Object[]{});
				if(list.size()>0){
					int jb = Integer.parseInt(list.get(0).get("XZQHJB").toString()) ;
					if(jb==0)
						result= "" ;
					else if(jb==1)
						result= xzqhdm.substring(0, 2) ;
					else if(jb==2)
						result= xzqhdm.substring(0, 4);
					else if(jb==3)
						result = xzqhdm.substring(0,6);
					else if(jb==4)
						result = xzqhdm.substring(0,9);
					else if(jb==5)
						result = xzqhdm;
					else if(jb==6)
						result = xzqhdm;
					else
						result = xzqhdm;
				}
				if("".equals(result)) continue;
				con.append(fieldName).append(" like '").append(result).append("%' ") ;
				if(i<xzqhdmList.length-1) {
					con.append(" or ") ;
				}
			}
			
			StringBuffer own = new StringBuffer();
			if(con.length()>0){
				if(uid!=null) {
					if(uid.length==2) {
						String fileName = uid[0] ;
						String userid = uid[1] ;
						own.append(" or ").append(fileName).append("=").append(userid) ;
					}
					
				}
				con.append(own) ;
				con.insert(0, " and (").append(")") ;
				
			}
			
			

			
		}finally{
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
			}
		}
		return con.toString();
	}
	
	
	public static String getXzqhqc(Connection conn,String xzqhdm) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		String res = "";
		try {
			sb.append("select xzqhqc from t_xzqh where XZQHDM ='")
					.append(xzqhdm).append("' ");
			ps = conn
					.prepareStatement(sb.toString(),
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			if (rs.next()) {
				res = rs.getString("xzqhqc");
			}
		} finally {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
		}
		return res;
	}
	
	public static Map<String,Object> getXzqh(Connection conn,String xzqhdm) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		Map<String,Object> res = new HashMap<String, Object>();
		try {
			sb.append("select xzqhdm,xzqhmc,zxjd,zxwd from t_xzqh where XZQHDM ='")
					.append(xzqhdm).append("' ");
			ps = conn
					.prepareStatement(sb.toString(),
							ResultSet.TYPE_SCROLL_SENSITIVE,
							ResultSet.CONCUR_READ_ONLY);
			rs = ps.executeQuery();
			if (rs.next()) {
				res.put("xzqhdm", rs.getString("xzqhdm"));
				res.put("xzqhmc", rs.getString("xzqhmc"));
				res.put("jd", rs.getString("zxjd"));
				res.put("wd", rs.getString("zxwd"));
			}
		} finally {
			if (rs != null)
				rs.close();
			if (ps != null)
				ps.close();
		}
		return res;
	}
	
	public static String getMultipleQueryXzqhConn(Connection conn,String xzqhdm,String fieldName,String ... uid) throws SQLException
	{
		String result="NODATA";
		PreparedStatement ps = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		StringBuffer con = new StringBuffer();
		try{
			if(xzqhdm==null || xzqhdm.equals("")){
				return  "" ;
			}
			String[] xzqhdmList = xzqhdm.split(";",-1) ;
			for(int i=0;i<xzqhdmList.length;i++) {
				xzqhdm = xzqhdmList[i] ;
				sb.delete(0, sb.length()) ;
				sb.append("select XZQHJB from T_XZQH where XZQHDM= '").append(xzqhdmList[i]).append("'");
				
				List<Map<String, Object>> list = queryForList(conn,sb.toString(), new Object[]{});
				if(list.size()>0){
					int jb = Integer.parseInt(list.get(0).get("XZQHJB").toString()) ;
					if(jb==0)
						result= "" ;
					else if(jb==1)
						result= xzqhdm.substring(0, 2) ;
					else if(jb==2)
						result= xzqhdm.substring(0, 4);
					else if(jb==3)
						result = xzqhdm.substring(0,6);
					else if(jb==4)
						result = xzqhdm.substring(0,9);
					else if(jb==5)
						result = xzqhdm;
					else if(jb==6)
						result = xzqhdm;
					else
						result = xzqhdm;
				}
				if("".equals(result)) continue;
				con.append(fieldName).append(" like '").append(result).append("%' ") ;
				if(i<xzqhdmList.length-1) {
					con.append(" or ") ;
				}
			}
			
			StringBuffer own = new StringBuffer();
			if(con.length()>0){
				if(uid!=null) {
					if(uid.length==2) {
						String fileName = uid[0] ;
						String userid = uid[1] ;
						own.append(" or ").append(fileName).append("=").append(userid) ;
					}
					
				}
				con.append(own) ;
				con.insert(0, " and (").append(")") ;
				
			}
			
			

			
		}finally{
			if(rs!=null){
				rs.close();
			}
			if(ps!=null){
				ps.close();
			}
		}
		return con.toString();
	}
	
	public static List<Map<String, Object>> queryForList(Connection conn, String sql, Object[] obj) {
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			if (obj != null && obj.length > 0) {
				for (int i = 0; i < obj.length; i++) {
					ps.setObject(i + 1, obj[i]);
				}
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			while (rs.next()) {
				Map<String, Object> map = new LinkedHashMap<String, Object>();
				for (int k = 1; k <= columnCount; k++) {
					map.put(rsmd.getColumnLabel(k), rs.getObject(k) == null ? "" : rs.getObject(k));
				}
				list.add(map);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		return list;
	}

	public static String getDownXzqh(String xzqhdm,String xzqhjb,String field) {
		int jb = Integer.parseInt(xzqhjb) ;
		StringBuffer sb = new StringBuffer() ;
		switch (jb) {
			case 0:
				if (field!=null) sb.append(" concat(substr(").append(field).append(",1,2),'') ") ;
				else sb.append(" concat(substr(").append(xzqhdm).append(",1,2),'') ") ;
				break;
			case 1:
				//sb.append(" concat(substr(").append(xzqhdm_fld).append(",1,4),'00000000') ") ;
				if (field!=null) sb.append(" concat(substr(").append(field).append(", 1, 4),'00000000') ") ;
				else sb.append(" if(concat(substr(").append(xzqhdm).append(", 1, 4),'00000000')='110100000000',concat(substr(").append(xzqhdm).append(", 1, 6),'000000'),concat(substr(").append(xzqhdm).append(", 1, 4),'00000000')) ") ;
				break;
			case 2:
				if (field!=null) sb.append(" concat(substr(").append(field).append(",1,6),'000000') ") ;
				else sb.append(" concat(substr(").append(xzqhdm).append(",1,6),'000000') ") ;
				break;
			case 3:
				if (field!=null) sb.append(" concat(substr(").append(field).append(",1,9),'000') ") ;
				else sb.append(" concat(substr(").append(xzqhdm).append(",1,9),'000') ") ;
				break;
			case 4:
				if (field!=null) sb.append(" concat(substr(").append(field).append(",1,12)) ") ;
				else sb.append(" concat(substr(").append(xzqhdm).append(",1,12)) ") ;
				break;
			default:
				if (field!=null) sb.append(" ").append(field).append(" ") ;
				else sb.append(" ").append(xzqhdm).append(" ") ;
				break;
		}
		return sb.toString() ;
	}
	
	
}
